package com.seu.vcampus.dao;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import com.seu.vcampus.vo.Product;

/**
 * 商品数据访问类
 * 负责商品数据的CRUD操作
 * 
 * @author 虚拟校园开发团队
 * @since 1.0.0
 */

public class ProductDao {
	private Connection connection;
	
	private static final String insertProduct = "INSERT INTO Product (pID, pName, stock, price, description, image) VALUES (?, ?, ?, ?, ?, ?)";
	private static final String selectProductByID = "SELECT * FROM Product WHERE pID = ?";
	private static final String selectProductByName = "SELECT * FROM Product WHERE pName = ?";
	private static final String selectAllProduct = "SELECT * FROM Product";
	private static final String deleteProduct = "DELETE FROM Product WHERE pID = ?";
	private static final String updateProduct = "UPDATE Product SET pName = ?, stock = ?, price = ?, description = ?, image = ? WHERE pID = ?";
	private static final String countProduct = "SELECT COUNT(*) FROM Product";
	private static final String updateProductStock = "UPDATE Product SET stock = ? WHERE pID = ?";
	
	public ProductDao(Connection connection)
	{
		this.connection = connection;
		initializeTable();
	}
	
	/**
	 * 初始化商品表
	 */
	private void initializeTable()
	{
		try(Statement stmt = connection.createStatement())
		{
			String createProductTable = "CREATE TABLE IF NOT EXISTS Product("
					+ "pID VARCHAR(50) PRIMARY KEY, "
					+ "pName VARCHAR(50) NOT NULL UNIQUE, "
					+ "stock INTEGER NOT NULL, "
					+ "price DOUBLE NOT NULL, "
					+ "description VARCHAR(500), "
					+ "image VARCHAR(255) NOT NULL"
					+ ")";
			stmt.execute(createProductTable);
		}
		catch(SQLException e)
		{
			throw new RuntimeException("Failed to initialize product table", e);
		}
	}
	
	/**
	 *  保存商品
	 *  @param p 商品对象
	 *  @return 是否保存成功
	 */
	public boolean save(Product p)
	{
		Product existingProduct = findById(p.getID());
		
		if(existingProduct == null)
		{
			try(PreparedStatement stmt = connection.prepareStatement(insertProduct))
			{
				stmt.setString(1, p.getID());
				stmt.setString(2, p.getName());
				stmt.setInt(3, p.getStock());
				stmt.setDouble(4, p.getPrice());
				stmt.setString(5, p.getDescription());
				stmt.setString(6, p.getImage());
				
				return stmt.executeUpdate() > 0;
			}
			catch(SQLException e)
			{
				throw new RuntimeException("Failed to save product", e);
			}
		}
		else
		{
			try(PreparedStatement stmt = connection.prepareStatement(updateProduct))
			{
				stmt.setString(1, p.getName());
				stmt.setInt(2, p.getStock());
				stmt.setDouble(3, p.getPrice());
				stmt.setString(4, p.getDescription());
				stmt.setString(5, p.getImage());
				stmt.setString(6, p.getID());
				
				return stmt.executeUpdate() > 0;
			}
			catch(SQLException e)
			{
				throw new RuntimeException("Failed to update product", e);
			}
		}
	}
	
	/**
	 * 根据商品ID查找商品
	 * @param pid 商品ID
	 * @return 商品对象，不存在返回null
	 */
	public Product findById(String pid)
	{
		try(PreparedStatement stmt = connection.prepareStatement(selectProductByID))
		{
			stmt.setString(1, pid);
			ResultSet rs = stmt.executeQuery();
			
			if(rs.next())
			{
				return mapResultSetToProduct(rs);
			}
			return null;
		}
		catch(SQLException e)
		{
			throw new RuntimeException("Failed to find product by ID", e);
		}
	}
	
	/**
	 * 根据商品名查找商品
	 * @param name 商品名
	 * @return 商品对象，不存在返回null
	 */
	public Product findByName(String name)
	{
		try(PreparedStatement stmt = connection.prepareStatement(selectProductByName))
		{
			stmt.setString(1, name);
			ResultSet rs = stmt.executeQuery();
			
			if(rs.next())
			{
				return mapResultSetToProduct(rs);
			}
			return null;
		}
		catch(SQLException e)
		{
			throw new RuntimeException("Failed to find product by name", e);
		}
	}
	
	/**
	 * 查找所有商品
	 * @return 商品列表
	 */
	public List<Product> findAll()
	{
		List<Product> products = new ArrayList<>();
				
		try(Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(selectAllProduct))
		{
			while(rs.next())
			{
				products.add(mapResultSetToProduct(rs));
			}
			return products;
		}
		catch(SQLException e)
		{
			throw new RuntimeException("Failed to find all products", e);
		}
	}
	
	/**
	 * 删除商品
	 * @param pid 商品ID
	 * @return 是否删除成功
	 */
	public boolean delete(String pid)
	{
		try(PreparedStatement stmt = connection.prepareStatement(deleteProduct))
		{
			stmt.setString(1, pid);
			return stmt.executeUpdate() > 0;
		}
		catch(SQLException e)
		{
			throw new RuntimeException("Failed to delete product", e);
		}
	}
	
	/**
	 * 获取商品数量
	 * @return 商品数量 
	 */
	public int getProductCount()
	{
		try (Statement stmt = connection.createStatement();
	            ResultSet rs = stmt.executeQuery(countProduct)) {
	            return rs.next() ? rs.getInt(1) : 0;
	        } catch (SQLException e) {
	            throw new RuntimeException("Failed to get product count", e);
	        }
	}
	
	/**
	 * 根据关键词搜索商品，关键词会在商品名和商品描述中匹配，按相关度降序返回
	 * @param keywords 搜索关键词（若存在多个关键词，按空格隔开）
	 * @return 按相关度排序的商品列表
	 */
	public List<Product> searchProducts(String keywords)
	{
		String[] words = keywords.trim().split("\\s+");
		
		StringBuilder relevanceBuilder = new StringBuilder();
		StringBuilder whereBuilder = new StringBuilder();
		
		for(int i = 0; i < words.length; ++i)
		{
			if(i > 0)
			{
				relevanceBuilder.append(" + ");
				whereBuilder.append(" OR ");
			}
			relevanceBuilder.append("(CASE WHEN instr(name, ?) > 0 THEN 1 ELSE 0 END)");
	        relevanceBuilder.append(" + (CASE WHEN instr(description, ?) > 0 THEN 1 ELSE 0 END)");
	        whereBuilder.append("instr(name, ?) > 0 OR instr(description, ?) > 0");
		}
		
		String sql = "SELECT pID, pName, description, (" + relevanceBuilder + ") AS relevance " + "FROM Product WHERE" + whereBuilder + " ORDER BY relevance DESC";
		
		try(PreparedStatement stmt = connection.prepareStatement(sql))
		{
			int idx = 1;
			for(String w : words)
			{
				stmt.setString(idx++, w);
				stmt.setString(idx++, w);
			}
			for(String w: words)
			{
				stmt.setString(idx++, w);
				stmt.setString(idx++, w);
			}
			try(ResultSet rs = stmt.executeQuery())
			{
				List<Product> ret = new ArrayList<>();
				while(rs.next())
				{
					ret.add(mapResultSetToProduct(rs));
				}
				return ret;
			}
			catch(SQLException e)
			{
				throw new RuntimeException("Failed to search products", e);
			}
		}
		catch(SQLException e)
		{
			throw new RuntimeException("Failed to search products", e);
		}
	}
	
	/**
	 * 更新库存
	 * @param pid 商品ID
	 * @param stock 新库存
	 * @return 是否更新成功
	 */
	public boolean updateProductStock(String pid, int stock)
	{
		try(PreparedStatement stmt = connection.prepareStatement(updateProductStock))
		{
			stmt.setInt(1, stock);
			stmt.setString(2, pid);
			return stmt.executeUpdate() > 0;
		}
		catch(SQLException e)
		{
			throw new RuntimeException("Failed to update product stock", e);
		}
	}
	
	/**
	 * 将ResultSet封装为Product对象
	 * @param rs 结果集
	 * @return Product对象
	 * @throws SQLException 
	 */
	public Product mapResultSetToProduct(ResultSet rs) throws SQLException
	{
		return new Product(rs.getString("pID"), rs.getString("pName"), rs.getInt("stock"), rs.getDouble("price"), rs.getString("description"), rs.getString("image"));
	}
}
